﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
namespace syc.Sale
{
    public partial class 辅料采购清单 : System.Web.UI.Page
    {
        double[] chanliang = new double[15];
        double[] danjia = new double[15];
        protected HSSFWorkbook hssfworkbook;
        protected void Page_Load(object sender, EventArgs e)
        {
            this.Message.Text = "·准备就绪";
            if (!this.IsPostBack)
            {
                this.NianDu.Text = DateTime.Today.Year.ToString();
                this.YueFen.Text = DateTime.Today.Month.ToString();
                string y = Request.QueryString["y"];
                string m = Request.QueryString["m"];

                if (y == null || m == null || this.NianDu.Items.FindByValue(y) == null || this.YueFen.Items.FindByValue(m) == null)
                {

                }
                else
                {
                    int year = int.Parse(y);
                    int month = int.Parse(m);
                    this.NianDu.Text = y;
                    this.YueFen.Text = m;

                    SqlConnection Conn = new SqlConnection();
                    Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
                    Conn.Open();
                    SqlCommand Cmd = new SqlCommand();
                    Cmd.Connection = Conn;

                    Cmd.CommandText = "select * from 辅料采购计划 where 年度=" + y + " and 月份=" + m;
                    Cmd.CommandType = CommandType.Text;
                    SqlDataReader Reader = Cmd.ExecuteReader();
                    if (Reader.Read())
                    {


                        this.BaoHuHuan1.Text = ((int)Reader["螺纹保护环5-1/2"]).ToString();
                        this.BaoHuHuan2.Text = ((int)Reader["螺纹保护环5-7/8"]).ToString();
                        this.XiShiJi.Text = ((double)Reader["稀释剂"]).ToString();
                        this.YouQi.Text = ((double)Reader["油漆"]).ToString();
                        this.YouPin.Text = ((double)Reader["油品"]).ToString();
                        this.DaBaoDai.Text = ((double)Reader["打包带"]).ToString();
                        this.LuoWenZhi.Text = ((double)Reader["螺纹脂"]).ToString();
                        this.SeHuaQi.Text = ((double)Reader["白色色环漆"]).ToString();
                        this.JieGuQi.Text = ((double)Reader["接箍漆"]).ToString();
                        this.BaiMo.Text = ((int)Reader["白墨"]).ToString();
                        this.CaoZhiDian.Text = ((int)Reader["草支垫"]).ToString();
                        this.QiTa.Text = ((double)Reader["其它"]).ToString();


                        this.BaoHuHuan1JiHuaZhiJinDanJia.Text = string.Format("{0:0.00}", Reader["螺纹保护环5-1/2单价"]);
                        this.BaoHuHuan2JiHuaZhiJinDanJia.Text = string.Format("{0:0.00}", Reader["螺纹保护环5-7/8单价"]);
                        this.XiShiJiJiHuaZhiJinDanJia.Text = string.Format("{0:0.00}", Reader["稀释剂单价"]);
                        this.YouQiJiHuaZhiJinDanJia.Text = string.Format("{0:0.00}", Reader["油漆单价"]);
                        this.YouPinJiHuaZhiJinDanJia.Text = string.Format("{0:0.00}", Reader["油品单价"]);
                        this.DaBaoDaiJiHuaZhiJinDanJia.Text = string.Format("{0:0.00}", Reader["打包带单价"]);
                        this.LuoWenZhiJiHuaZhiJinDanJia.Text = string.Format("{0:0.00}", Reader["螺纹脂单价"]);
                        this.SeHuaQiJiHuaZhiJinDanJia.Text = string.Format("{0:0.00}", Reader["白色色环漆单价"]);
                        this.JieGuQiJiHuaZhiJinDanJia.Text = string.Format("{0:0.00}", Reader["接箍漆单价"]);
                        this.BaiMoJiHuaZhiJinDanJia.Text = string.Format("{0:0.00}", Reader["白墨单价"]);
                        this.CaoZhiDianJiHuaZhiJinDanJia.Text = string.Format("{0:0.00}", Reader["草支垫单价"]);
                        this.QiTaJiHuaZhiJinDanJia.Text = string.Format("{0:0.00}", Reader["其它单价"]);


                        this.BaoHuHuan1JiHuaZhiJinJine.Text = string.Format("{0:0.00}", Reader["螺纹保护环5-1/2金额"]);
                        this.BaoHuHuan2JiHuaZhiJinJine.Text = string.Format("{0:0.00}", Reader["螺纹保护环5-7/8金额"]);
                        this.XiShiJiJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.XiShiJiJiHuaZhiJinDanJia.Text) * (double)Reader["稀释剂"]));
                        this.YouQiJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.YouQiJiHuaZhiJinDanJia.Text) * (double)Reader["油漆"]));
                        this.YouPinJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.YouPinJiHuaZhiJinDanJia.Text) * (double)Reader["油品"]));
                        this.DaBaoDaiJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.DaBaoDaiJiHuaZhiJinDanJia.Text) * (double)Reader["打包带"]));
                        this.LuoWenZhiJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.LuoWenZhiJiHuaZhiJinDanJia.Text) * (double)Reader["螺纹脂"]));
                        this.SeHuaQiJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.SeHuaQiJiHuaZhiJinDanJia.Text) * (double)Reader["白色色环漆"]));
                        this.JieGuQiJiHuaZhiJinJine.Text = string.Format("{0:0.00}", Reader["接箍漆金额"]);
                        this.BaiMoJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.BaiMoJiHuaZhiJinDanJia.Text) * (int)Reader["白墨"]));
                        this.CaoZhiDianJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.CaoZhiDianJiHuaZhiJinDanJia.Text) * (int)Reader["草支垫"]));
                        this.YouPinJiHuaZhiJinJine.Text = string.Format("{0:0.00}", Reader["油品金额"]);
                        this.QiTaJiHuaZhiJinJine.Text = string.Format("{0:0.00}", Reader["其它金额"]);
                        this.NianDu.Enabled = false;
                        this.YueFen.Enabled = false;
                    }
                    Reader.Close();

                    Conn.Close();
                    Reader.Dispose();
                    Cmd.Dispose();
                    Conn.Dispose();


                }
            }
        }
        private bool CheckInput()
        {

            if (!double.TryParse(this.BaoHuHuan1.Text, out chanliang[1]))
            {
                this.Message.Text = "·辅料螺纹保护环5-1/2 计划购进量录入不正确";
                return false;
            }
            if (!double.TryParse(this.BaoHuHuan2.Text, out chanliang[2]))
            {
                this.Message.Text = "·辅料螺纹保护环5-7/8 计划购进量录入不正确";
                return false;
            }
            if (!double.TryParse(this.XiShiJi.Text, out chanliang[3]))
            {
                this.Message.Text = "·辅料稀释剂 计划购进量录入不正确";
                return false;
            }
            if (!double.TryParse(this.YouQi.Text, out chanliang[4]))
            {
                this.Message.Text = "·辅料油漆 计划购进量录入不正确";
                return false;
            }
            //if (!double.TryParse(this.YouPin.Text, out chanliang[7]))
            //{
            //    this.Message.Text = "·辅料油品 计划产量录入不正确";
            //    return false;
            //}
            if (!double.TryParse(this.DaBaoDai.Text, out chanliang[6]))
            {
                this.Message.Text = "·辅料打包带 计划购进量录入不正确";
                return false;
            }
            if (!double.TryParse(this.LuoWenZhi.Text, out chanliang[7]))
            {
                this.Message.Text = "·辅料螺纹脂 计划购进量录入不正确";
                return false;
            }
            if (!double.TryParse(this.SeHuaQi.Text, out chanliang[8]))
            {
                this.Message.Text = "·辅料白色色环漆 计划购进量录入不正确";
                return false;
            }
            if (!double.TryParse(this.JieGuQi.Text, out chanliang[9]))
            {
                this.Message.Text = "·辅料接箍漆 计划购进量录入不正确";
                return false;
            }
            if (!double.TryParse(this.BaiMo.Text, out chanliang[10]))
            {
                this.Message.Text = "·辅料白墨 计划购进量录入不正确";
                return false;
            }
            if (!double.TryParse(this.CaoZhiDian.Text, out chanliang[11]))
            {
                this.Message.Text = "·辅料草支垫 计划购进量录入不正确";
                return false;
            }
            //if (!double.TryParse(this.QiTa.Text, out chanliang[14]))
            //{
            //    this.Message.Text = "·辅料其它 计划产量录入不正确";
            //    return false;
            //}


            if (!double.TryParse(this.BaoHuHuan1JiHuaZhiJinDanJia.Text, out danjia[1]))
            {
                this.Message.Text = "·辅料螺纹保护环5-1/2 螺纹保护环5-1/2单价录入不正确";
                return false;
            }
            if (!double.TryParse(this.BaoHuHuan2JiHuaZhiJinDanJia.Text, out danjia[2]))
            {
                this.Message.Text = "·辅料螺纹保护环5-7/8 螺纹保护环5-7/8单价录入不正确";
                return false;
            }
            if (!double.TryParse(this.XiShiJiJiHuaZhiJinDanJia.Text, out danjia[3]))
            {
                this.Message.Text = "·辅料稀释剂 稀释剂单价录入不正确";
                return false;
            }
            if (!double.TryParse(this.YouQiJiHuaZhiJinDanJia.Text, out danjia[4]))
            {
                this.Message.Text = "·辅料油漆 油漆单价录入不正确";
                return false;
            }
            //if (!double.TryParse(this.YouPin.TextJiHuaZhiJinDanJia, out danjia[7]))
            //{
            //    this.Message.Text = "·辅料油品 油品单价录入不正确";
            //    return false;
            //}
            if (!double.TryParse(this.DaBaoDaiJiHuaZhiJinDanJia.Text, out danjia[6]))
            {
                this.Message.Text = "·辅料打包带 打包带单价录入不正确";
                return false;
            }
            if (!double.TryParse(this.LuoWenZhiJiHuaZhiJinDanJia.Text, out danjia[7]))
            {
                this.Message.Text = "·辅料螺纹脂 螺纹脂单价录入不正确";
                return false;
            }
            if (!double.TryParse(this.SeHuaQiJiHuaZhiJinDanJia.Text, out danjia[8]))
            {
                this.Message.Text = "·辅料白色色环漆 白色色环漆单价录入不正确";
                return false;
            }
            if (!double.TryParse(this.JieGuQiJiHuaZhiJinDanJia.Text, out danjia[9]))
            {
                this.Message.Text = "·辅料接箍漆 接箍漆单价录入不正确";
                return false;
            }
            if (!double.TryParse(this.BaiMoJiHuaZhiJinDanJia.Text, out danjia[10]))
            {
                this.Message.Text = "·辅料白墨 白墨单价录入不正确";
                return false;
            }
            if (!double.TryParse(this.CaoZhiDianJiHuaZhiJinDanJia.Text, out danjia[11]))
            {
                this.Message.Text = "·辅料草支垫 辅料草支垫单价录入不正确";
                return false;
            }
            //if (!double.TryParse(this.QiTaJiHuaZhiJinDanJia.Text, out danjia[14]))
            //{
            //    this.Message.Text = "·辅料其它 其它录入单价不正确";
            //    return false;
            //}
            return true;
        }
        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("模板/辅料采购计划.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "辅料采购计划";
            hssfworkbook.SummaryInformation = si;
        }
        protected void WriteToFile()
        {
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode("辅料采购计划") + ".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }
        protected void Button_Save_Click(object sender, EventArgs e)
        {

            if (!CheckInput())
            {
                return;
            }
            if (this.YouPinJiHuaZhiJinJine.Text == "" || this.QiTaJiHuaZhiJinJine.Text == "")
            {
                this.YouPinJiHuaZhiJinJine.Text = "0";
                this.QiTaJiHuaZhiJinJine.Text = "0";
            }
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;

            if (this.NianDu.Enabled)
            {

                Cmd.CommandText =
"if EXISTS(select * from 辅料采购计划 where 年度=" + this.NianDu.Text + " and 月份=" + this.YueFen.Text + ")\n"
+ "begin\n"
+ "return\n"
+ "end\n"
+ "else\n"
+ "begin\n"
+ @"INSERT INTO [辅料采购计划]
           ([年度]
           ,[月份]          
        
           ,[螺纹保护环5-1/2]
           ,[螺纹保护环5-7/8]
           ,[稀释剂]
           ,[油漆]
           ,[油品]
           ,[打包带]
           ,[螺纹脂]
           ,[白色色环漆]
           ,[接箍漆]
           ,[白墨]
           ,[草支垫]
           ,[其它]          
           ,[套管成品接箍单价]
           ,[螺纹保护环5-1/2单价]
           ,[螺纹保护环5-7/8单价]
           ,[稀释剂单价]
           ,[油漆单价]
           ,[油品单价]
           ,[打包带单价]
           ,[螺纹脂单价]
           ,[白色色环漆单价]
           ,[接箍漆单价]
           ,[白墨单价]
           ,[草支垫单价]
           ,[其它单价]          
           ,[套管成品接箍金额]
           ,[螺纹保护环5-1/2金额]
           ,[螺纹保护环5-7/8金额]
           ,[稀释剂金额]
           ,[油漆金额]
           ,[油品金额]
           ,[打包带金额]
           ,[螺纹脂金额]
           ,[白色色环漆金额]
           ,[接箍漆金额]
           ,[白墨金额]
           ,[草支垫金额]
           ,[其它金额])"
+ "values(" + this.NianDu.Text + "," + this.YueFen.Text

+ "," + chanliang[1]
+ "," + chanliang[2]
+ "," + chanliang[3]
+ "," + chanliang[4]
+ "," + chanliang[5]
+ "," + chanliang[6]
+ "," + chanliang[7]
+ "," + chanliang[8]
+ "," + chanliang[9]
+ "," + chanliang[10]
+ "," + chanliang[11]
+ "," + chanliang[12]
+ "," + danjia[0]
+ "," + danjia[1]
+ "," + danjia[2]
+ "," + danjia[3]
+ "," + danjia[4]
+ "," + danjia[5]
+ "," + danjia[6]
+ "," + danjia[7]
+ "," + danjia[8]
+ "," + danjia[9]
+ "," + danjia[10]
+ "," + danjia[11]
+ "," + danjia[12]


+ "," + (chanliang[1] * danjia[1])
+ "," + (chanliang[2] * danjia[2])
+ "," + (chanliang[3] * danjia[3])
+ "," + (chanliang[4] * danjia[4])
+ "," + (double.Parse(this.YouPinJiHuaZhiJinJine.Text))
+ "," + (chanliang[6] * danjia[6])
+ "," + (chanliang[7] * danjia[7])
+ "," + (chanliang[8] * danjia[8])
+ "," + (chanliang[9] * danjia[9])
+ "," + (chanliang[10] * danjia[10])
+ "," + (chanliang[11] * danjia[11])
+ "," + (double.Parse(this.QiTaJiHuaZhiJinJine.Text))
+ ")\n"
+ "end";
            }
            else
            {
                Cmd.CommandText =
"update 辅料采购计划 set [螺纹保护环5-1/2]=" + chanliang[1]
+ ",[螺纹保护环5-7/8]=" + chanliang[2]
+ ",[稀释剂]=" + chanliang[3]
+ ",[油漆]=" + chanliang[4]
+ ",[油品]=0"
+ ",[打包带]=" + chanliang[6]
+ ",[螺纹脂]=" + chanliang[7]
+ ",[白色色环漆]=" + chanliang[8]
+ ",[接箍漆]=" + chanliang[9]
+ ",[白墨]=" + chanliang[10]
+ ",[草支垫]=" + chanliang[11]
+ ",[其它]=0"

+ ",[螺纹保护环5-1/2单价]=" + danjia[1]
+ ",[螺纹保护环5-7/8单价]=" + danjia[2]
+ ",[稀释剂单价]=" + danjia[3]
+ ",[油漆单价]=" + danjia[4]
+ ",[油品单价]=0"
+ ",[打包带单价]=" + danjia[6]
+ ",[螺纹脂单价]=" + danjia[7]
+ ",[白色色环漆单价]=" + danjia[8]
+ ",[接箍漆单价]=" + danjia[9]
+ ",[白墨单价]=" + danjia[10]
+ ",[草支垫单价]=" + danjia[11]
+ ",[其它单价]=0"

+ ",[螺纹保护环5-1/2金额]=" + (chanliang[1] * danjia[1])
+ ",[螺纹保护环5-7/8金额]=" + (chanliang[2] * danjia[2])
+ ",[稀释剂金额]=" + (chanliang[3] * danjia[3])
+ ",[油漆金额]=" + (chanliang[4] * danjia[4])
+ ",[油品金额]=" + (double.Parse(this.YouPinJiHuaZhiJinJine.Text))
+ ",[打包带金额]=" + (chanliang[6] * danjia[6])
+ ",[螺纹脂金额]=" + (chanliang[7] * danjia[7])
+ ",[白色色环漆金额]=" + (chanliang[8] * danjia[8])
+ ",[接箍漆金额]=" + (chanliang[9] * danjia[9])
+ ",[白墨金额]=" + (chanliang[10] * danjia[10])
+ ",[草支垫金额]=" + (chanliang[11] * danjia[11])
+ ",[其它金额]=" + (double.Parse(this.QiTaJiHuaZhiJinJine.Text))
+ " where 年度="
+ this.NianDu.Text + " and 月份=" + this.YueFen.Text;
            }


            if (Cmd.ExecuteNonQuery() < 1)
            {
                if (this.NianDu.Enabled)
                {
                    this.Message.Text = "·保存失败，计划已经存在";
                }
                else
                {
                    this.Message.Text = "·保存失败，您正在修改的计划可能已经被删除";
                }
            }
            else
            {
                this.Message.Text = "·保存成功";
                Button_Calc_Click(null, null);
                this.NianDu.Enabled = false;
                this.YueFen.Enabled = false;
            }

            Conn.Close();
            Cmd.Dispose();
            Conn.Dispose();

        }

        protected void Button_Calc_Click(object sender, EventArgs e)
        {
            if (CheckInput())
            {

                this.BaoHuHuan1JiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.BaoHuHuan1JiHuaZhiJinDanJia.Text) * (int.Parse(this.BaoHuHuan1.Text))));
                this.BaoHuHuan2JiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.BaoHuHuan2JiHuaZhiJinDanJia.Text) * (int.Parse(this.BaoHuHuan2.Text))));
                this.XiShiJiJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.XiShiJiJiHuaZhiJinDanJia.Text) * (double.Parse(this.XiShiJi.Text))));
                this.YouQiJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.YouQiJiHuaZhiJinDanJia.Text) * (double.Parse(this.YouQi.Text))));
                this.DaBaoDaiJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.DaBaoDaiJiHuaZhiJinDanJia.Text) * (double.Parse(this.DaBaoDai.Text))));
                this.LuoWenZhiJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.LuoWenZhiJiHuaZhiJinDanJia.Text) * (double.Parse(this.LuoWenZhi.Text))));
                this.SeHuaQiJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.SeHuaQiJiHuaZhiJinDanJia.Text) * (double.Parse(this.SeHuaQi.Text))));
                this.JieGuQiJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.JieGuQiJiHuaZhiJinDanJia.Text) * (double.Parse(this.JieGuQi.Text))));
                this.BaiMoJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.BaiMoJiHuaZhiJinDanJia.Text) * (int.Parse(this.BaiMo.Text))));
                this.CaoZhiDianJiHuaZhiJinJine.Text = string.Format("{0:0.00}", (double.Parse(this.CaoZhiDianJiHuaZhiJinDanJia.Text) * (int.Parse(this.CaoZhiDian.Text))));

            }
        }
    }
}
